package com.example.demo01.Impl.webImpl;

import com.example.demo01.Dao.webDao.ServiceCenterDao;
import com.example.demo01.Entity.abstractclass.AbstractServiceCenter;
import com.example.demo01.Entity.webEntity.HospitalEntity;
import com.example.demo01.Entity.webEntity.ServicePoint;
import com.example.demo01.Entity.webEntity.ServicePointWithDis;
import com.example.demo01.utils.JDBCPGUtils;
import com.example.demo01.utils.JSONPGUtils;
import org.springframework.stereotype.Repository;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

@Repository
public class ServiceCenterDaoImpl implements ServiceCenterDao {


    /**
     * 获取所有的医院服务点
     *
     * @return 医院服务点List集合
     */
    @Override
    public List<AbstractServiceCenter> getAllHospital() {
        //结果集
        List<AbstractServiceCenter> serviceCenterList = new ArrayList<>();
        AbstractServiceCenter serviceCenter = null;
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        //SQL语句
        String sql = "select hospital.name,hospital.address,hospital.province,hospital.city,hospital.domain,hospital.lng,hospital.lat,hospital.geom\n" +
                "from hospital";
        //数据库操作
        try {
            connection = JDBCPGUtils.getConnection();
            statement = connection.createStatement();
            //执行SQL语句，获取结果集
            resultSet = statement.executeQuery(sql);
            //解析结果集合
            while (resultSet.next()) {
                serviceCenter = new HospitalEntity(
                        resultSet.getString(1),
                        resultSet.getString(2),
                        resultSet.getString(3),
                        resultSet.getString(4),
                        resultSet.getString(5),
                        resultSet.getFloat(6),
                        resultSet.getFloat(7),
                        resultSet.getString(8)
                );
                serviceCenterList.add(serviceCenter);
            }
        } catch (SQLException e1) {
            e1.printStackTrace();
        } finally {
            //释放数据库连接资源
            JDBCPGUtils.close(resultSet, statement, connection);
        }
        return serviceCenterList;
    }

    /**
     * 获取所有的服务中心服务点
     *
     * @return 医院服务点List集合
     */
    @Override
    public List<AbstractServiceCenter> getAllServicePoint() {
        //结果集
        List<AbstractServiceCenter> serviceCenterList = new ArrayList<>();
        AbstractServiceCenter serviceCenter = null;
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        //SQL语句
        String sql = "select servicecenter.name,servicecenter.address,servicecenter.province,servicecenter.city,servicecenter.domain,servicecenter.lng,servicecenter.lat,servicecenter.geom\n" +
                "from servicecenter";
        //数据库操作
        try {
            connection = JDBCPGUtils.getConnection();
            statement = connection.createStatement();
            //执行SQL语句，获取结果集
            resultSet = statement.executeQuery(sql);
            //解析结果集合
            while (resultSet.next()) {
                serviceCenter = new ServicePoint(
                        resultSet.getString(1),
                        resultSet.getString(2),
                        resultSet.getString(3),
                        resultSet.getString(4),
                        resultSet.getString(5),
                        resultSet.getFloat(6),
                        resultSet.getFloat(7),
                        resultSet.getString(8)
                );
                serviceCenterList.add(serviceCenter);
            }
        } catch (SQLException e1) {
            e1.printStackTrace();
        } finally {
            //释放数据库连接资源
            JDBCPGUtils.close(resultSet, statement, connection);
        }
        return serviceCenterList;
    }

    /**
     * 根据经纬度默认获取所有的医疗点
     * @param lng 经度
     * @param lat 纬度
     * @return 带有距离值信息的医疗点
     */
    @Override
    public List<AbstractServiceCenter> getDefaultHospitalPsWidthoutDis(String lng, String lat) {
        String sql="select \n" +
                "\tROW_NUMBER() over(ORDER bY hospital.uid DESC)  as id, \n" +
                "\tname,\n" +
                "\tlng,\n" +
                "\tlat,\n" +
                "\tprovince||','||city||','||domain as domain,\n" +
                "\tST_Distance(ST_Transform(hospital.geom,3857),A.geom) as distance\n" +
                "from hospital,(select ST_Transform(ST_SetSRID(ST_GeomFromText('POINT("+ lng + " "+lat+")'),4326),3857) as geom) A\n" +
                "order by  distance asc";
        System.out.println(lng+"\t"+lat);
        return getResultListWithDisBySQL(sql);
    }

    /**
     * 根据经纬度默认获取分部点
     * @param lng 经度
     * @param lat 纬度
     * @return 带有距离值信息的服务点
     * */
    @Override
    public List<AbstractServiceCenter> getHospitalPsByBufferDis(String lng, String lat, String distance) {
        //SQL语句
        String sql="select \n" +
                "\tROW_NUMBER() over(ORDER bY hospital.uid DESC)  as id, \n" +
                "\tname,\n" +
                "\tlng,\n" +
                "\tlat,\n" +
                "\tprovince||','||city||','||domain as domain,\n" +
                "\tST_Distance(ST_Transform(hospital.geom,3857),A.geom) as distance\n" +
                "from hospital,(select ST_Transform(ST_SetSRID(ST_GeomFromText('POINT("+lng+" "+ lat + ")'),4326),3857) as geom) A\n" +
                "where ST_Distance(ST_Transform(hospital.geom,3857),A.geom)<="+distance+"\n" +
                "order by  distance asc";
        //更新temp.json文件
        System.out.println(lng+"\t"+lat);
        JSONPGUtils.getBufferJSONByLonLat(lng, lat, distance);
        return getResultListWithDisBySQL(sql);
    }

    /**
     * 根据经纬度和缓冲区距离获取医疗点
     * @param lng 经度
     * @param lat 纬度
     * @param distance 缓冲区距离
     * @return
     */
    @Override
    public List<AbstractServiceCenter> getServicePsByBufferDis(String lng, String lat, String distance) {
        String sql="select \n" +
                "\tROW_NUMBER() over(ORDER bY servicecenter.uid DESC)  as id, \n" +
                "\tname,\n" +
                "\tlng,\n" +
                "\tlat,\n" +
                "\tprovince||','||city||','||domain as domain,\n" +
                "\tST_Distance(ST_Transform(servicecenter.geom,3857),A.geom) as distance\n" +
                "from servicecenter,(select ST_Transform(ST_SetSRID(ST_GeomFromText('POINT("+ lng +" "+ lat + ")'),4326),3857) as geom) A\n" +
                "where ST_Distance(ST_Transform(servicecenter.geom,3857),A.geom)<=" + distance +"\n" +
                "order by  distance asc";
        //更新temp.json文件
        System.out.println(lng+"\t"+lat);
        JSONPGUtils.getBufferJSONByLonLat(lng, lat, distance);
        return getResultListWithDisBySQL(sql);
    }

    /**
     * 根据经纬度默认获取所有的服务点
     *
     * @param lng 经度
     * @param lat 纬度
     * @return 带有距离值信息的服务点
     */
    @Override
    public List<AbstractServiceCenter> getDefaultServicePsWidthoutDis(String lng, String lat) {
        //SQL语句
        String sql = "select \n" +
                "\tROW_NUMBER() over(ORDER bY servicecenter.uid DESC)  as id, \n" +
                "\tname,\n" +
                "\tlng,\n" +
                "\tlat,\n" +
                "\tprovince||','||city||','||domain as domain,\n" +
                "\tST_Distance(ST_Transform(servicecenter.geom,3857),A.geom) as distance\n" +
                "from servicecenter,(select ST_Transform(ST_SetSRID(ST_GeomFromText('POINT(" + lng + " " + lat +
                ")'),4326),3857) as geom) A\n" +
                "order by  distance asc";
        return getResultListWithDisBySQL(sql);
    }

    /**
     * 根据Sql语句解析结果集合
     * @param sql
     * @return 包含距离信息的结果集
     */
    public List<AbstractServiceCenter> getResultListWithDisBySQL(String sql){
        //结果集
        List<AbstractServiceCenter> serviceCenterList = new ArrayList<>();
        AbstractServiceCenter serviceCenter = null;
        //声明变量
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        //数据库操作
        try {
            connection = JDBCPGUtils.getConnection();
            statement = connection.prepareStatement(sql);
            //执行查询，获取结果集
            resultSet = statement.executeQuery();
            //解析结果集
            int count=1;
            while (resultSet.next()) {
                serviceCenter = new ServicePointWithDis(
                        count++,
                        resultSet.getString("name"),
                        resultSet.getString("domain"),
                        resultSet.getFloat("lng"),
                        resultSet.getFloat("lat"),
                        resultSet.getFloat("distance")
                );
                serviceCenterList.add(serviceCenter);
            }
            return serviceCenterList;
        } catch (SQLException e) {
            e.printStackTrace();
            return serviceCenterList;
        } finally {
            //数据库资源释放
            JDBCPGUtils.close(resultSet, statement, connection);
        }
    }
}
